package com.boss.salary.dao.impl;


import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IFinancialAuditDao;
import com.boss.salary.vo.FinancialAuditVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class FinancialAuditDaoImpl extends BaseDaoImpl implements IFinancialAuditDao {


    @Override
    public List<FinancialAuditVO> queryFinancialAuditPage(PaginationDTO paginationDTO, Map<String, Object> queryParam,
                                                            String wheresql, List<String> childrenId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();


        sql.append(" SELECT ");
        sql.append(" av.agency_id id, ");
        sql.append(" av.salary_month, ");
        sql.append(" av.agency_code, ");
        sql.append(" av.agency_name, ");
        sql.append(" av.biz_key, ");
        sql.append(" av.is_thirteen, ");
        sql.append(" av.salary_info_verified_code verifyStatus, ");
        sql.append(" count( sme.id ) personNum, ");
        sql.append(" av.audit_explain, ");
        sql.append(" count( CASE pe.state WHEN '3' THEN 1 ELSE null END ) adminNum, ");
        sql.append(" count( CASE merit_pay_code WHEN '1' THEN 1 ELSE null END ) causeNum, ");
        sql.append(" count( CASE pe.state WHEN '1' THEN 1 ELSE null END ) quitNum, ");
        sql.append(" sum( sme.s1 ) realNum, ");
        sql.append(" sum( CASE pe.state WHEN '3' THEN sme.s1  ELSE 0 END ) adminReal, ");
        sql.append(" sum( CASE merit_pay_code WHEN '1' THEN sme.s1  ELSE 0 END ) causeReal, ");
        sql.append(" sum( CASE pe.state WHEN '1' THEN sme.s1  ELSE 0 END ) quitReal, ");
        sql.append(" sum( sme.s2 ) shouldNum, ");
        sql.append(" sum( CASE pe.state WHEN '3' THEN sme.s2  ELSE 0 END ) adminShould, ");
        sql.append(" sum( CASE merit_pay_code WHEN '1' THEN sme.s2  ELSE 0 END ) causeShould, ");
        sql.append(" sum( CASE pe.state WHEN '1' THEN sme.s2  ELSE 0 END ) quitShould  ");
        sql.append(" FROM ");
        if("-2".equals(queryParam.get("flag"))){//查询已退回数据可能包括退回到单位的数据，属于未上报数据
            sql.append(TableName.SALARY_MONTH_END).append("  sme")
                    .append(" join ").append(TableName.PERSON_EXT).append("  pe ");
        }else{
            sql.append(TableName.SALARY_MONTH_END_SUBMIT).append("  sme")
                    .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append("  pe ");
        }
        sql.append(" on  sme.person_id=pe.person_id and sme.agency_id=pe.agency_id ");
//        if(!"-2".equals(queryParam.get("flag"))) {
//            sql.append(" and sme.is_thirteen=pe.is_thirteen ");
//        }
        sql.append(" join ").append(TableName.AGENCY_VERIFY).append("  av")
                .append(" on sme.agency_id=av.agency_id and pe.agency_id=av.agency_id and sme.is_thirteen=av.is_thirteen")

                .append(" where  av.is_deleted=? AND pe.is_deleted = 0 ")
                .append("  and pe.is_stop_salary =? ");

        paramList.add(SystemDictConstant.IS_FALSE);

        paramList.add(SystemDictConstant.IS_FALSE);
        if(!"-2".equals(queryParam.get("flag"))) {//查询已退回数据可能包括退回到单位的数据，属于未上报数据
            sql.append(" and av.is_salary_submit =? ");
            paramList.add(SystemDictConstant.IS_SUBMIT);
        }
        sql.append(" and av.is_month_end=? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        if (!"all".equals(queryParam.get("flag"))) {
//            sql.append(" and ").append(SysUtils.inSql("av.biz_key", bizKeys));
            sql.append("  and av.biz_key = '236ba9e4a21944b072cedc129560b6db'");
        }
        sql.append(" and ").append(SysUtils.inSql("sme.agency_id", childrenId));
        // 单位变动状态
        if (!StringUtils.isEmpty(queryParam.get("changeAgency"))) {
            if(queryParam.get("changeAgency").equals("0")){
                sql.append(" and av.is_salary_change = 0 ");
            } else {
                sql.append(" and av.is_salary_change != 0 ");
            }
        }
        // 工资类别
        if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
            sql.append(" and pe.merit_pay_code = ? ");
            paramList.add(queryParam.get("salaryType"));
        }
        if (!StringUtils.isEmpty(queryParam.get("province"))) {
            sql.append(" and sme.mof_div_code = ? ");
            paramList.add(queryParam.get("province"));
        }
        sql.append(" GROUP BY av.is_thirteen, av.agency_id, av.salary_month, av.agency_code, av.agency_name, av.biz_key, av.salary_info_verified_code, av.audit_explain,av.biz_key,av.is_thirteen ");
        sql.append(" order by agency_code ");

        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(FinancialAuditVO.class), params);
    }

    @Override
    public int queryFinancialAuditTotal(Map<String, Object> queryParam, String wheresql, List<String> childrenId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select COUNT(*) ")
                .append(" from ").append(TableName.AGENCY_VERIFY).append("  av")

                .append(" where av.is_deleted=? ");

        paramList.add(SystemDictConstant.IS_FALSE);
        sql.append(" and ").append(SysUtils.inSql("av.agency_id", childrenId));
        if(!"-2".equals(queryParam.get("flag"))) {//查询已退回数据可能包括退回到单位的数据，属于未上报数据
            sql.append(" and av.is_salary_submit =? ");
            paramList.add(SystemDictConstant.IS_SUBMIT);
        }
        sql.append(" and av.is_month_end=? ");
        paramList.add(SystemDictConstant.IS_FALSE);
//        if (!"all".equals(queryParam.get("flag"))) {
//            sql.append(wheresql);
//        }
//        // 单位变动状态
        if (!StringUtils.isEmpty(queryParam.get("changeAgency"))) {
            if(queryParam.get("changeAgency").equals("0")){
                sql.append(" and av.is_salary_change = 0 ");
            } else {
                sql.append(" and av.is_salary_change != 0 ");
            }
        }
        if (!StringUtils.isEmpty(queryParam.get("province"))) {
            sql.append(" and av.mof_div_code = ?");
            paramList.add(queryParam.get("province"));
        }

        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }

//    @Override
//    public int queryFinancialAuditTotal(Map<String, Object> queryParam, String wheresql, List<String> childrenId) {
//        List<Object> paramList = new ArrayList<>();
//        StringBuilder sql = new StringBuilder();
//        sql.append(" SELECT COUNT(*) FROM ");
//
//        sql.append(TableName.AGENCY_VERIFY).append(" av ");
//
//        sql.append(" where av.is_month_end = ? ");
//
//        sql.append(" and av.is_thirteen = ? ");
//        sql.append(" AND av.`is_deleted` = 0 ");
//        paramList.add(SystemDictConstant.IS_FALSE);
//        paramList.add(SystemDictConstant.IS_FALSE);
//        if(!"-2".equals(queryParam.get("flag"))) {//已退回查询包括未上报数据 -2为查询已退回
//            sql.append(" and av.is_salary_submit = ? ");
//            paramList.add(SystemDictConstant.IS_TRUE);
//        }
//
//        if (!"all".equals(queryParam.get("flag"))) {
//            sql.append(wheresql);
//        }
//        sql.append(" and ").append(SysUtils.inSql("av.agency_id", childrenId));
//        // 单位变动状态
//        if (!StringUtils.isEmpty(queryParam.get("changeAgency"))) {
//            if(queryParam.get("changeAgency").equals("0")){
//                sql.append(" and av.is_salary_change = 0 ");
//            } else {
//                sql.append(" and av.is_salary_change != 0 ");
//            }
//        }
//        // 工资类别
//        /*if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
//            sql.append("and pe.merit_pay_code = ?");
//            paramList.add(queryParam.get("salaryType"));
//        }
//        sql.append(" GROUP BY ai.id, sme.id ) t");*/
//        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
//    }


    @Override
    public FinancialAuditVO queryFinancialAuditDataTotal(Map<String, Object> queryParam, List<String> bizKeys, List<String> childrenId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ");
        sql.append(" sum(case state when '3' then personNum else 0 end) adminNum, ");
        sql.append(" sum(case state when '1' then personNum else 0 end) quitNum, ");
        sql.append(" sum(case state when '3' then realMoney else 0 end) adminReal, ");
        sql.append(" sum(case state when '1' then realMoney else 0 end) quitReal, ");
        sql.append(" sum(case state when '3' then should else 0 end) adminShould, ");
        sql.append(" sum(case state when '1' then should else 0 end) quitShould, ");
        sql.append(" sum(personNum) personNum, ");
        sql.append(" sum(realMoney) realNum, ");
        sql.append(" sum(should) shouldNum ");
        sql.append(" FROM ( ");
        sql.append(" SELECT ");
        sql.append(" ai.id, ");
        sql.append(" ai.agency_code, ");
        sql.append(" ai.agency_name, ");
        sql.append("    av.biz_key,  ");
        sql.append(" pe.state, ");
        sql.append(" count(sme.id) personNum, ");
        sql.append(" sum(sme.s1) realMoney, ");
        sql.append(" sum(sme.s2) should ");
        sql.append(" FROM ")
                .append(TableName.SALARY_MONTH_END_SUBMIT).append(" as sme")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" as pe ")
                .append(" on  sme.person_id=pe.person_id and sme.agency_id=pe.agency_id ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" as av")
                .append(" on sme.agency_id=av.agency_id and pe.agency_id=av.agency_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" as ai ")
                .append(" on ai.id=av.agency_id ")
                .append(" where ai.is_deleted=? and av.is_deleted=? AND pe.`is_deleted` = 0 ")
                .append(" and av.is_thirteen=? and pe.is_stop_salary =? ")
                .append(" and av.is_salary_submit =? and av.is_month_end=?");
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        paramList.add(SystemDictConstant.IS_FALSE);
        if (!"all".equals(queryParam.get("flag"))) {
            sql.append(" and ").append(SysUtils.inSql("av.biz_key", bizKeys));
        }
        sql.append(" and ").append(SysUtils.inSql("sme.agency_id", childrenId));
        // 单位变动状态
        if (!StringUtils.isEmpty(queryParam.get("changeAgency"))) {
            if(queryParam.get("changeAgency").equals("0")){
                sql.append(" and av.is_salary_change = 0 ");
            } else {
                sql.append(" and av.is_salary_change != 0 ");
            }
        }
        // 工资类别
        if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
            sql.append("and pe.merit_pay_code = ?");
            paramList.add(queryParam.get("salaryType"));
        }
        sql.append(" GROUP BY  ai.id, ai.agency_code, ai.agency_name, av.biz_key, pe.state ");
        sql.append(" ORDER BY  ai.agency_code ) c ");
        return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(FinancialAuditVO.class), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> getPayMoneyControlWarningMessage(String agencyId, Double ctrlNum) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append(" SELECT person_name from ")
                .append(TableName.SALARY_MONTH_END_SUBMIT).append(" sme join ")
                .append(TableName.PERSON_EXT_SUBMIT).append(" pet on sme.person_id=pet.person_id ")
                .append(" where sme.agency_id=? and s1>? ");
        paramList.add(agencyId);
        paramList.add(ctrlNum);
        return super.getjdbcTemplate().queryForList(sql.toString(),paramList.toArray());
    }
}
